﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace VerificationCode.Dal
{
    public  class WenZhi
    {
        private DBHelper dbHelper = new DBHelper();
        /// <summary>
        /// 构造函数
        /// </summary>
        public WenZhi()
        {
        }
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="model">VerificationCode.Model.WenZhi实体类</param>
        /// <returns>新增记录的ID</returns>
        public int Add(VerificationCode.Model.WenZhi model)
        {
            if (Get(model.Code) == null)
            {
                string sql = @"INSERT INTO wenzhi
				(BuShouCode,Code,Text) 
				VALUES(@BuShouCode,@Code,@Text);
				SELECT LAST_INSERT_ID();";
            MySqlParameter[] parameters = new MySqlParameter[]{
                model.BuShouCode == null ? new MySqlParameter("@BuShouCode", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@BuShouCode", MySqlDbType.VarChar, 255) { Value = model.BuShouCode },
                model.Code == null ? new MySqlParameter("@Code", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@Code", MySqlDbType.VarChar, 255) { Value = model.Code },
                model.Text == null ? new MySqlParameter("@Text", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@Text", MySqlDbType.VarChar, 255) { Value = model.Text }
            };
            int maxID;
            return int.TryParse(dbHelper.ExecuteScalar(sql, parameters), out maxID) ? maxID : -1;
            }
            return 0;
        }
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">VerificationCode.Model.WenZhi实体类</param>
        public int Update(VerificationCode.Model.WenZhi model)
        {
            string sql = @"UPDATE wenzhi SET 
				BuShouCode=@BuShouCode,Code=@Code,Text=@Text
				WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                model.BuShouCode == null ? new MySqlParameter("@BuShouCode", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@BuShouCode", MySqlDbType.VarChar, 255) { Value = model.BuShouCode },
                model.Code == null ? new MySqlParameter("@Code", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@Code", MySqlDbType.VarChar, 255) { Value = model.Code },
                model.Text == null ? new MySqlParameter("@Text", MySqlDbType.VarChar, 255) { Value = DBNull.Value } : new MySqlParameter("@Text", MySqlDbType.VarChar, 255) { Value = model.Text },
                new MySqlParameter("@ID", MySqlDbType.Int32, 11){ Value = model.ID }
            };
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        public int Delete(int id)
        {
            string sql = "DELETE FROM wenzhi WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", MySqlDbType.Int32, 11){ Value = id }
            };
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 将DataRedar转换为List
        /// </summary>
        private List<VerificationCode.Model.WenZhi> DataReaderToList(MySqlDataReader dataReader)
        {
            List<VerificationCode.Model.WenZhi> List = new List<VerificationCode.Model.WenZhi>();
            VerificationCode.Model.WenZhi model = null;
            while (dataReader.Read())
            {
                model = new VerificationCode.Model.WenZhi();
                model.ID = dataReader.GetInt32(0);
                if (!dataReader.IsDBNull(1))
                    model.BuShouCode = dataReader.GetString(1);
                if (!dataReader.IsDBNull(2))
                    model.Code = dataReader.GetString(2);
                if (!dataReader.IsDBNull(3))
                    model.Text = dataReader.GetString(3);
                List.Add(model);
            }
            return List;
        }
        /// <summary>
        /// 查询所有记录
        /// </summary>
        public List<VerificationCode.Model.WenZhi> GetAll()
        {
            string sql = "SELECT * FROM wenzhi";
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql);
            List<VerificationCode.Model.WenZhi> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
        /// <summary>
        /// 查询记录数
        /// </summary>
        public long GetCount()
        {
            string sql = "SELECT COUNT(*) FROM wenzhi";
            long count;
            return long.TryParse(dbHelper.GetFieldValue(sql), out count) ? count : 0;
        }
        /// <summary>
        /// 根据主键查询一条记录
        /// </summary>
        public VerificationCode.Model.WenZhi Get(int id)
        {
            string sql = "SELECT * FROM wenzhi WHERE ID=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", MySqlDbType.Int32, 11){ Value = id }
            };
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<VerificationCode.Model.WenZhi> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }

        /// <summary>
        /// 根据主键查询一条记录
        /// </summary>
        public VerificationCode.Model.WenZhi Get(string id)
        {
            string sql = "SELECT * FROM wenzhi WHERE Code=@ID";
            MySqlParameter[] parameters = new MySqlParameter[]{
                new MySqlParameter("@ID", MySqlDbType.VarChar, 11){ Value = id }
            };
            MySqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<VerificationCode.Model.WenZhi> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }

        /// <summary>
        /// 获取验证码
        /// </summary>
        public List<VerificationCode.Model.WenZhi> GetCodeText()
        {
            const string sql = "SELECT * FROM wenzhi ORDER BY RAND() LIMIT 4";
            var dataReader = dbHelper.GetDataReader(sql);
            var list = DataReaderToList(dataReader);
            dataReader.Close();
            return list;
        }

        /// <summary>
        /// 获取答案备选
        /// </summary>
        /// <param name="buShouCode"></param>
        /// <param name="id"></param>
        /// <param name="number">数量</param>
        /// <returns></returns>
        public List<VerificationCode.Model.WenZhi> GetAnswer(string buShouCode, int id,int number=1)
        {
            string sql = $"SELECT * FROM wenzhi where BuShouCode='{buShouCode}' and ID <> {id} ORDER BY RAND() LIMIT "+ number;
            var dataReader = dbHelper.GetDataReader(sql);
            var list = DataReaderToList(dataReader);
            dataReader.Close();
            return list;
        }
    }
}
